﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Entidades;
using System.Configuration;

namespace DAO
{
    public class SalaDAO
    {
        public static void Insertar(Sala sala)
        {
            
            SqlTransaction tran = null;
            string sql = "insert into Sala (nombre, tipoSala) values (@nombre, @tipo); select @@identity;";
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.AddWithValue("@nombre",sala.Nombre);
            cmd.Parameters.AddWithValue("@tipo",sala.TipoSala);

            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            try
            {

                cn.Open();
                tran = cn.BeginTransaction();
                cmd.CommandText = sql;
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                //int idSala = int.Parse(cmd.ExecuteScalar().ToString());

                tran.Commit();
               
            }
            catch (Exception)
            {
                if (tran != null)
                    tran.Rollback();
            }
            finally
            {
                if (cn != null && cn.State == ConnectionState.Open)
                    cn.Close();
            }

        }
        public static void Update(Sala sala)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "update Sala set nombre=@nom, tipoSala=@tipo where numero_Sala=@numero";
            cmd.Parameters.AddWithValue("@nom", sala.Nombre);
            cmd.Parameters.AddWithValue("@numero",sala.Numero);
            cmd.Parameters.AddWithValue("@tipo", sala.TipoSala);
            cmd.ExecuteNonQuery();
            cn.Close();

        }

        public static Sala BuscarPorId(int idSala)
        {
            
            Sala sal = null;

            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            cn.Open();

            SqlCommand cmd1 = new SqlCommand();
            cmd1.Connection = cn;


            cmd1.CommandText = "select numero_Sala,nombre, tipoSala from Sala where numero_Sala=@Sala";
            
            cmd1.Parameters.AddWithValue("@Sala", idSala);

            SqlDataReader dr = cmd1.ExecuteReader();
            while (dr.Read())
            {
                sal = new Sala();
                sal.Numero = int.Parse(dr["numero_Sala"].ToString());
                sal.Nombre = dr["nombre"].ToString();  
                sal.TipoSala=dr["tipoSala"].ToString();
            }
            
            cn.Close();

            return sal;

        }

        public static void darBajaSala(int idSala)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            cn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "delete from Sala where numero_Sala=@id";
            cmd.Parameters.Add(new SqlParameter("@id", idSala));
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public static DataTable cargarGrilla(string orden)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            cn.Open();
            DataTable dt= new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "Select numero_Sala, nombre, tipoSala from Sala";
            if (orden == "")
                cmd.CommandText += " order by nombre";
            else
            {
                cmd.CommandText += " order by " + orden;
            }
            SqlDataReader dr = cmd.ExecuteReader();
            dt.Load(dr);

            cn.Close();

            return dt;
         }
        public static DataTable cargarGrillaNombre(string nombre)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            cn.Open();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select numero_Sala, nombre, tipoSala from Sala where nombre like @nom";
            cmd.Parameters.AddWithValue("@nom", "%" + nombre + "%");
            SqlDataReader dr = cmd.ExecuteReader();
            dt.Load(dr);
            cn.Close();
            return dt;
        }

        public static DataTable cargarDropSala()
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            cn.Open();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select numero_Sala, nombre, tipoSala from Sala";
            SqlDataReader dr = cmd.ExecuteReader();
            dt.Load(dr);
            cn.Close();
            return dt;
        }

        public static DataTable cargarGrillaChec(int chec)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            cn.Open();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select numero_Sala, nombre, tipoSala from Sala where numero_Sala=@idChec";
            cmd.Parameters.AddWithValue("@idChec",chec);
            SqlDataReader dr = cmd.ExecuteReader();
            dt.Load(dr);
            cn.Close();
            return dt;
        }
    }
}
